/**
 * 账单服务
 * 提供账单相关的业务逻辑
 */
const { Bill, Category, User } = require('../models')
const { Op } = require('sequelize')
const sequelize = require('../config/database')
const CustomException = require('../utils/customException')
const ResultCodeEnum = require('../enums/ResultCodeEnum')
const authConfig = require('../config/auth')
const jwt = require('jsonwebtoken')
const ExcelJS = require('exceljs')
const path = require('path')
const fs = require('fs')
class BillService {
    /**
     * 格式化数字，保留两位小数
     * @param {number} num - 要格式化的数字
     * @returns {number} 格式化后的数字
     */
    formatDouble(num) {
        if (typeof num !== 'number' || isNaN(num)) {
            return 0;
        }
        return Math.round(num * 100) / 100;
    }

	// 身份验证已在中间件完成，不需要此方法
	// isLogin(token) {
	// 	if (!token) {
	// 		throw new CustomException(ResultCodeEnum.USER_NOT_LOGIN)
	// 	}

	// 	try {
	// 		const decoded = jwt.verify(token, authConfig.jwt.secret)
	// 		return {
	// 			id: decoded.id
	// 		}
	// 	} catch (error) {
	// 		if (error.name === 'TokenExpiredError') {
	// 			throw new CustomException(ResultCodeEnum.TOKEN_EXPIRED_ERROR)
	// 		} else {
	// 			throw new CustomException(ResultCodeEnum.TOKEN_INVALID_ERROR)
	// 		}
	// 	}
	// }

	/**
	 * 处理浮点数精度问题
	 * @param {number} value - 需要处理的浮点数
	 * @returns {number} 处理后的浮点数，保留两位小数
	 */
	formatDouble(value) {
		return Math.round(value * 100) / 100
	}

	/**
	 * 添加账单
	 * @param {Object} billData - 账单数据
	 * @param {number} userId - 用户ID
	 * @returns {Promise<Object>} 创建的账单对象
	 * @throws {CustomException} 如果添加失败
	 */
	async addBill(billData, userId) {
		try {
			// 检查用户是否存在
			console.log('添加账单 - 传入的userId:', userId);
			const user = await User.findByPk(userId);
			console.log('添加账单 - 查询用户结果:', user);
			if (!user) {
				console.log('添加账单 - 用户不存在:', userId);
				throw new CustomException(ResultCodeEnum.USER_NOT_EXIST_ERROR, '用户不存在');
			}

			// 检查分类是否存在
			const category = await Category.findByPk(billData.categoryId);
			if (!category) {
				console.log('添加账单 - 分类不存在:', billData.categoryId);
				throw new CustomException(ResultCodeEnum.CATEGORY_NOT_EXIST_ERROR, '分类不存在');
			}

			// 设置备注
			let remark = billData.remark || '';
			if (!remark) {
				remark = category.name;
			}

			// 创建账单
			const bill = await Bill.create({
				user_id: userId,
				category_id: billData.categoryId,
				amount: this.formatDouble(billData.amount),
				type: billData.type,
				remark: remark,
				bill_date: billData.billDate,
				create_time: new Date()
			})

			return bill
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('添加账单异常:', error)
				throw new CustomException(ResultCodeEnum.PARAM_ERROR)
			}
		}
	}

	/**
	 * 获取最近账单
	 * @param {number} userId - 用户ID
	 * @param {number} limit - 限制数量，默认10条
	 * @returns {Promise<Array>} 账单列表
	 * @throws {CustomException} 如果查询失败
	 */
	/**
	 * 获取最近账单
	 * @param {number} userId - 用户ID
	 * @param {number} limit - 限制数量，默认10条
	 * @returns {Promise<Array>} 账单列表
	 * @throws {CustomException} 如果查询失败
	 */
	async getRecentBills(userId, limit = 10) {
		try {
			console.log('获取最近账单 - 用户ID:', userId, '限制数量:', limit)
			const bills = await Bill.findAll({
				where: { user_id: userId },
				include: [
					{
						model: Category,
						as: 'category',
						attributes: ['id', 'name', 'icon']
					}
				],
				order: [
					['bill_date', 'DESC'],
					['create_time', 'DESC']
				],
				limit: limit
			})

			console.log('获取最近账单成功，数量:', bills.length)
			return bills
		} catch (error) {
			if (error instanceof CustomException) {
				throw error;
			} else {
				console.error('获取最近账单异常:', error);
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '获取最近账单失败: ' + error.message);
			}
		}
	}

	/**
	 * 获取账单列表（分页）
	 * @param {number} userId - 用户ID
	 * @param {number} page - 页码
	 * @param {number} size - 每页数量
	 * @returns {Promise<Object>} 包含账单列表和总条数的结果
	 * @throws {CustomException} 如果查询失败
	 */
	async getBillList(userId, page, size) {
		try {
			const offset = (page - 1) * size;
			
			const result = await Bill.findAndCountAll({
				where: { user_id: userId },
				include: [
					{
						model: Category,
						as: 'category',
						attributes: ['id', 'name', 'icon']
					}
				],
				order: [
					['bill_date', 'DESC'],
					['create_time', 'DESC']
				],
				limit: size,
				offset: offset
			});
			
			return {
				bills: result.rows,
				total: result.count,
				page: page,
				size: size,
				totalPages: Math.ceil(result.count / size)
			};
		} catch (error) {
			if (error instanceof CustomException) {
				throw error;
			} else {
				console.error('获取账单列表异常:', error);
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '获取账单列表失败: ' + error.message);
			}
		}
	}


	/**
	 * 获取最近账单
	 * @param {number} userId - 用户ID
	 * @param {number} limit - 限制数量，默认10条
	 * @returns {Promise<Array>} 账单列表
	 * @throws {CustomException} 如果查询失败
	 */
	async getRecentBills(userId, limit = 10) {
		try {
			console.log('获取最近账单 - 用户ID:', userId, '限制数量:', limit)
			const bills = await Bill.findAll({
				where: { user_id: userId },
				include: [
					{
						model: Category,
						as: 'category',
						attributes: ['id', 'name', 'icon']
					}
				],
				order: [
				['bill_date', 'DESC'],
				['create_time', 'DESC']
			],
			limit: limit
		})

		console.log('获取最近账单成功，数量:', bills.length)
		return bills
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('获取最近账单异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '获取最近账单失败: ' + error.message)
			}
		}
	}

	/**
	 * 按日期查询账单
	 * @param {number} userId - 用户ID
	 * @param {number} year - 年份
	 * @param {number} month - 月份(1-12)
	 * @returns {Promise<Object>} 按天分组的账单数据
	 * @throws {CustomException} 如果查询失败
	 */
	async getBillsByDate(userId, year, month) {
		try {
			// 设置查询的开始日期（当月1号）
			const startDate = new Date(year, month - 1, 1)
			// 设置查询的结束日期（下月1号）
			const endDate = new Date(year, month, 1)

			// 查询当月所有账单
			const bills = await Bill.findAll({
				where: {
					user_id: userId,
					bill_date: {
						[Op.gte]: startDate,
						[Op.lt]: endDate
					}
				},
				include: [
					{
						model: Category,
						as: 'category',
						attributes: ['id', 'name', 'icon']
					}
				],
				order: [['bill_date', 'DESC']]
			})

			// 按天分组账单
			const dailyBills = new Map()
			const dayFormat = new Intl.DateTimeFormat('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' })

			// 计算当月总收入和支出
			let monthlyIncome = 0.0
			let monthlyExpense = 0.0

			for (const bill of bills) {
				// 获取日期字符串作为分组键
				const billDate = new Date(bill.bill_date)
				const dayKey = dayFormat.format(billDate)

				// 如果该日期不存在，创建新列表
				if (!dailyBills.has(dayKey)) {
					dailyBills.set(dayKey, [])
				}

				// 添加账单到对应日期
				dailyBills.get(dayKey).push(bill)

				// 累计收入和支出 - 确保amount是有效数字
				const amount = bill.amount !== null && bill.amount !== undefined ? Number(bill.amount) : 0
				const validAmount = isNaN(amount) ? 0 : amount
				if (bill.type === 'income') {
					monthlyIncome += validAmount
				} else {
					monthlyExpense += validAmount
				}
			}

			// 格式化金额
			monthlyIncome = this.formatDouble(monthlyIncome)
			monthlyExpense = this.formatDouble(monthlyExpense)
			const monthlySurplus = this.formatDouble(monthlyIncome - monthlyExpense)

			// 构建返回结果
			const result = {
				dailyBills: Object.fromEntries(dailyBills),
				monthlyIncome,
				monthlyExpense,
				monthlySurplus,
				totalCount: bills.length,
				year,
				month
			}

			console.log('按日期查询账单成功 - 用户ID:', userId, '年份:', year, '月份:', month, '账单数量:', bills.length)
			return result
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('按日期查询账单异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '按日期查询账单失败: ' + error.message)
			}
		}
	}
	/**
	 * 获取最近6个月的收入支出数据，用于柱状图展示
	 * @param {number} userId - 用户ID
	 * @returns {Promise<Object>} 包含月份、收入和支出数据的结果
	 * @throws {CustomException} 如果查询失败
	 */
	async getMonthlyStatistics(userId) {
		try {
			// 获取当前月份和年份
			const cal = new Date()
			let currentMonth = cal.getMonth()
			let currentYear = cal.getFullYear()

			// 准备返回数据
			const categories = []
			const incomeData = []
			const expenseData = []

			// 当月收入、支出
			let currentMonthIncome = 0.0
			let currentMonthExpense = 0.0

			// 计算前6个月的数据
			for (let i = 5; i >= 0; i--) {
				// 计算月份和年份
				let month = currentMonth - i
				let year = currentYear

				// 处理月份为负数的情况
				while (month < 0) {
					month += 12
					year -= 1
				}

				// 设置查询的开始日期（当月1号）
				const startDate = new Date(year, month, 1)

				// 设置查询的结束日期（下月1号）
				const endDate = new Date(year, month + 1, 1)

				// 查询当月所有收入账单
				const incomeBills = await Bill.findAll({
					where: {
						user_id: userId,
						type: 'income', // 收入
						bill_date: {
							[Op.gte]: startDate,
							[Op.lt]: endDate
						}
					}
				})

				// 计算总收入
				const totalIncome = incomeBills.reduce((sum, bill) => {
					const amount = bill.amount !== null && bill.amount !== undefined ? Number(bill.amount) : 0
					const validAmount = isNaN(amount) ? 0 : amount
					return sum + validAmount
				}, 0)

				// 查询当月所有支出账单
				const expenseBills = await Bill.findAll({
					where: {
						user_id: userId,
						type: 'expense', // 支出
						bill_date: {
							[Op.gte]: startDate,
							[Op.lt]: endDate
						}
					}
				})

				// 计算总支出
				const totalExpense = expenseBills.reduce((sum, bill) => {
					const amount = bill.amount !== null && bill.amount !== undefined ? Number(bill.amount) : 0
					const validAmount = isNaN(amount) ? 0 : amount
					return sum + validAmount
				}, 0)

				// 如果是当前月份，记录收入和支出
				if (i === 0) {
					currentMonthIncome = totalIncome
					currentMonthExpense = totalExpense
				}

				// 格式化月份名称
				const monthName = `${month + 1}月`
				categories.push(monthName)

				// 添加收入和支出数据
				incomeData.push(this.formatDouble(totalIncome))
				expenseData.push(this.formatDouble(totalExpense))
			}

			// 计算当月盈余
			const currentMonthSurplus = this.formatDouble(currentMonthIncome - currentMonthExpense)

			// 构建返回数据结构
			const chartData = {
				categories,
				expenses: this.formatDouble(currentMonthExpense),
				income: this.formatDouble(currentMonthIncome),
				surplus: currentMonthSurplus,
				series: [
					{
						name: '支出',
						data: expenseData
					},
					{
						name: '收入',
						data: incomeData
					}
				]
			}

			return chartData
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('获取月度统计数据异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR)
			}
		}
	}

	/**
	 * 导出账单数据到Excel文件
	 * @param {number} userId - 用户ID
	 * @param {Object} options - 可选参数
	 * @param {Date} options.startDate - 开始日期
	 * @param {Date} options.endDate - 结束日期
	 * @returns {Promise<string>} 导出的Excel文件路径
	 * @throws {CustomException} 如果导出失败
	 */
	async exportBills(userId, options = {}) {
		try {
			// 检查用户是否存在
			const user = await User.findByPk(userId);
			if (!user) {
				throw new CustomException(ResultCodeEnum.USER_NOT_EXIST_ERROR, '用户不存在');
			}

			// 构建查询条件
			const where = {
				user_id: userId
			};

			// 如果提供了日期范围，添加到查询条件
			if (options.startDate && options.endDate) {
				where.bill_date = {
					[Op.between]: [options.startDate, options.endDate]
				};
			} else if (options.startDate) {
				where.bill_date = {
					[Op.gte]: options.startDate
				};
			} else if (options.endDate) {
				where.bill_date = {
					[Op.lte]: options.endDate
				};
			}

			// 查询账单数据，包括关联的分类信息
			const bills = await Bill.findAll({
				where,
				include: [{
					model: Category,
					as: 'category',
					attributes: ['name']
				}],
				order: [['bill_date', 'DESC']]
			});

			// 创建Excel工作簿和工作表
			const workbook = new ExcelJS.Workbook();
			const worksheet = workbook.addWorksheet('账单数据');

			// 设置表头
			worksheet.columns = [
				{ header: '账单ID', key: 'id', width: 10 },
				{ header: '日期', key: 'bill_date', width: 20 },
				{ header: '分类', key: 'category_name', width: 15 },
				{ header: '类型', key: 'type', width: 10 },
				{ header: '金额', key: 'amount', width: 15 },
				{ header: '备注', key: 'remark', width: 30 }
			];

			// 添加数据行
			bills.forEach(bill => {
				worksheet.addRow({
					id: bill.id,
					bill_date: bill.bill_date.toLocaleString(),
					category_name: bill.category ? bill.category.name : '未分类',
					type: bill.type === 'income' ? '收入' : '支出',
					amount: bill.amount,
					remark: bill.remark || ''
				});
			});

			// 生成文件名和路径
			const timestamp = new Date().getTime();
			const filename = `bills_export_${userId}_${timestamp}.xlsx`;
			const filePath = path.join(__dirname, '../../exports', filename);

			// 确保导出目录存在
			const exportDir = path.join(__dirname, '../../exports');
			if (!fs.existsSync(exportDir)) {
				fs.mkdirSync(exportDir, { recursive: true });
			}

			// 保存工作簿
			await workbook.xlsx.writeFile(filePath);

			return filePath;
		} catch (error) {
			if (error instanceof CustomException) {
				throw error;
			} else {
				console.error('导出账单失败:', error);
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '导出账单失败');
			}
		}
	}

	/**
	 * 按月份统计各分类支出，用于饼图展示
	 * @param {number} userId - 用户ID
	 * @param {number} year - 年份
	 * @param {number} month - 月份(1-12)
	 * @returns {Promise<Object>} 各分类支出统计数据
	 * @throws {CustomException} 如果查询失败
	 */
	async stats(userId, year, month) {
		try {
			// 验证用户是否登录
			if (!userId) {
				throw new CustomException(ResultCodeEnum.USER_NOT_LOGIN)
			}

			if (!year || !month) {
				throw new CustomException(ResultCodeEnum.PARAM_ERROR)
			}

			console.log('Stats method called with:', {userId, year, month})

			// 保存this引用
			const self = this

			// 设置查询的开始日期（当月1号）
			const startDate = new Date(year, month - 1, 1) // 月份从0开始，所以要减1

			// 设置查询的结束日期（下月1号）
			const endDate = new Date(year, month, 1)

			// 查询当月所有支出账单
			const bills = await Bill.findAll({
				where: {
					user_id: userId,
					type: 'expense', // 只查询支出
					bill_date: {
						[Op.gte]: startDate,
						[Op.lt]: endDate
					}
				},
				include: [
					{
						model: Category,
						as: 'category',
						attributes: ['id', 'name', 'icon', 'bg_class']
					}
				]
			})

			// 查询当月所有收入账单
			const incomeBills = await Bill.findAll({
				where: {
					user_id: userId,
					type: 'income', // 只查询收入
					bill_date: {
						[Op.gte]: startDate,
						[Op.lt]: endDate
					}
				}
			})

			// 计算总收入
		console.log('Income bills count:', incomeBills.length)
		const totalIncome = incomeBills.reduce((sum, bill) => {
			const amount = bill.amount !== null && bill.amount !== undefined ? Number(bill.amount) : 0
			// 确保amount是有效数字
			const validAmount = isNaN(amount) ? 0 : amount
			console.log('Adding income:', validAmount)
			return sum + validAmount
		}, 0)
		console.log('Calculated totalIncome:', totalIncome)

			// 按分类ID分组统计
			const categoryAmounts = new Map()
			let totalExpense = 0.0

			console.log('Expense bills count:', bills.length)

			// 统计各分类支出金额
			for (const bill of bills) {
				const categoryId = bill.category_id
				const amount = bill.amount

				console.log('Processing bill:', {categoryId, amount})

				// 确保amount是有效数字
				const validAmount = amount !== null && amount !== undefined ? Number(amount) : 0
				const finalAmount = isNaN(validAmount) ? 0 : validAmount

				// 累加分类金额
				if (categoryAmounts.has(categoryId)) {
					const currentAmount = categoryAmounts.get(categoryId)
					categoryAmounts.set(categoryId, currentAmount + finalAmount)
					console.log(`Updated category ${categoryId}: ${currentAmount} + ${finalAmount} = ${currentAmount + finalAmount}`)
				} else {
					categoryAmounts.set(categoryId, finalAmount)
					console.log(`Added category ${categoryId}: ${finalAmount}`)
				}

				// 累加总支出 - 确保amount是数值类型
				totalExpense += finalAmount
				console.log(`Total expense so far: ${totalExpense}`)
			}

			console.log('Final totalExpense:', totalExpense)

			// 计算结余
			const surplus = self.formatDouble(totalIncome - totalExpense)
			console.log('Calculated surplus:', surplus)

			// 构建饼图数据和分类详情
			const pieData = []
			const categoryDetails = []

			// 先查询所有分类信息
			const allCategories = await Category.findAll({
				attributes: ['id', 'name', 'icon', 'bg_class']
			})

			for (const [categoryId, amount] of categoryAmounts.entries()) {
				// 查找分类信息
				const category = allCategories.find(cat => cat.id === categoryId)

				if (category) {
					// 计算占比
					const percentage = totalExpense > 0 ? (amount / totalExpense) * 100 : 0

					// 构建饼图数据项
					pieData.push({
						name: category.name,
						value: self.formatDouble(amount)
					})

					// 构建分类详情
					categoryDetails.push({
						categoryId: category.id,
						name: category.name,
						icon: category.icon,
						bg_class: category.bg_class,
						amount: self.formatDouble(amount),
						percentage: `${percentage.toFixed(2)}%`
					})
				}
			}

			// 按金额降序排序
			categoryDetails.sort((a, b) => {
				return b.amount - a.amount // 降序
			})

			// 构建返回结果
			const result = {
				series: [
					{
						data: pieData
					}
				],
				categoryDetails,
				totalExpense: self.formatDouble(totalExpense),
				totalIncome: self.formatDouble(totalIncome),
				surplus,
				year,
				month
			}

			console.log('Stats result:', result)
			return result
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('按月份统计分类支出异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR)
			}
		}
	}

	/**
	 * 查询指定年份每月的收支数据，用于折线图展示
	 * @param {number} userId - 用户ID
	 * @param {number} year - 年份
	 * @returns {Promise<Object>} 每月收支数据
	 * @throws {CustomException} 如果查询失败
	 */
	async trend(userId, year) {
		try {
			if (!year) {
				throw new CustomException(ResultCodeEnum.PARAM_ERROR)
			}

			// 初始化12个月的收入和支出数据，默认为0
			const incomeData = new Array(12).fill(0.0)
			const expenseData = new Array(12).fill(0.0)

			// 设置查询的开始日期（当年1月1日）
			const startDate = new Date(year, 0, 1) // 月份从0开始，0表示1月

			// 设置查询的结束日期（下一年1月1日）
			const endDate = new Date(year + 1, 0, 1)

			// 查询该年所有账单
			const bills = await Bill.findAll({
				where: {
					user_id: userId,
					bill_date: {
						[Op.gte]: startDate,
						[Op.lt]: endDate
					}
				}
			})

			// 按月份统计收入和支出
			for (const bill of bills) {
				// 获取月份（0-11）
				const month = new Date(bill.bill_date).getMonth()

				// 确保amount是有效数字
				const amount = bill.amount !== null && bill.amount !== undefined ? Number(bill.amount) : 0;
				const validAmount = isNaN(amount) ? 0 : amount;

				// 根据类型累加收入或支出
				if (bill.type === 'income') {
					// 收入
					incomeData[month] += validAmount
				} else if (bill.type === 'expense') {
					// 支出
					expenseData[month] += validAmount
				}
			}

			// 格式化所有月份的数据，解决精度问题
			for (let i = 0; i < 12; i++) {
				incomeData[i] = this.formatDouble(incomeData[i])
				expenseData[i] = this.formatDouble(expenseData[i])
			}

			// 构建返回数据结构
			return {
				series: [
					{
						name: '支出',
						data: expenseData
					},
					{
						name: '收入',
						data: incomeData
					}
				],
				year
			}
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('查询年度收支趋势异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR)
			}
		}
	}

	/**
	 * 更新账单
	 * @param {number} billId - 账单ID
	 * @param {number} userId - 用户ID
	 * @param {Object} billData - 账单数据
	 * @returns {Promise<Object>} 更新后的账单对象
	 * @throws {CustomException} 如果更新失败
	 */
	async updateBill(billId, userId, billData) {
		try {
			// 查找账单
			const bill = await Bill.findByPk(billId)

			// 检查账单是否存在且属于当前用户
			if (!bill || bill.user_id !== userId) {
				throw new CustomException(ResultCodeEnum.BILL_NOT_FOUND_OR_NOT_BELONG_TO_USER)
			}

			// 更新账单
			await bill.update({
				category_id: billData.categoryId,
				amount: this.formatDouble(billData.amount),
				type: billData.type,
				remark: billData.remark || '',
				bill_date: billData.billDate
			})

			return bill
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('更新账单异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '更新账单失败: ' + error.message)
			}
		}
	}

	/**
	 * 删除账单
	 * @param {number} billId - 账单ID
	 * @param {number} userId - 用户ID
	 * @returns {Promise<void>}
	 * @throws {CustomException} 如果删除失败
	 */
	async deleteBill(billId, userId) {
		try {
			// 查找账单
			const bill = await Bill.findByPk(billId)

			// 检查账单是否存在且属于当前用户
			if (!bill || bill.user_id !== userId) {
				throw new CustomException(ResultCodeEnum.BILL_NOT_FOUND_OR_NOT_BELONG_TO_USER)
			}

			// 删除账单
			await bill.destroy()
		} catch (error) {
			if (error instanceof CustomException) {
				throw error
			} else {
				console.error('删除账单异常:', error)
				throw new CustomException(ResultCodeEnum.SYSTEM_ERROR, '删除账单失败: ' + error.message)
			}
		}
	}
}

module.exports = new BillService()
